//! 数据库管理模块
//! 
//! 这个模块负责数据库连接管理和数据访问层的实现。
//! 使用SQLx提供类型安全的数据库操作。

use sqlx::PgPool;
use std::time::Duration;
use uuid::Uuid;

use crate::{
    config::DatabaseConfig,
    models::{User, CreateUserRequest, UpdateUserRequest, UserQuery, PaginatedResponse},
    errors::{AppError, AppResult},
};

/// 数据库管理器
/// 
/// 负责管理PostgreSQL数据库连接池和提供数据访问方法
#[derive(Clone)]
pub struct DatabaseManager {
    /// 数据库连接池
    pub pool: PgPool,
    /// 数据库配置
    config: DatabaseConfig,
}

impl DatabaseManager {
    /// 创建新的数据库管理器
    /// 
    /// # 参数
    /// - `config`: 数据库配置
    /// 
    /// # 返回值
    /// - `Result<Self, AppError>`: 创建成功返回管理器，失败返回错误
    pub async fn new(config: &DatabaseConfig) -> AppResult<Self> {
        tracing::info!("🔗 初始化数据库连接池...");

        // 创建连接池
        let pool = config.database_pool_options()
            .connect(&config.url)
            .await
            .map_err(AppError::Database)?;

        // 运行数据库迁移
        sqlx::migrate!("./migrations")
            .run(&pool)
            .await
            .map_err(|e| AppError::Database(sqlx::Error::Migrate(Box::new(e))))?;

        tracing::info!("✅ 数据库连接池初始化完成");
        Ok(Self {
            pool,
            config: config.clone(),
        })
    }

    /// 健康检查
    /// 
    /// 检查数据库连接是否正常
    /// 
    /// # 返回值
    /// - `Result<(), AppError>`: 健康检查成功返回Ok，失败返回错误
    pub async fn health_check(&self) -> AppResult<()> {
        sqlx::query("SELECT 1")
            .execute(&self.pool)
            .await
            .map_err(AppError::Database)?;
        Ok(())
    }

    /// 获取连接池大小信息
    /// 
    /// # 返回值
    /// - `(usize, usize)`: (活跃连接数, 空闲连接数)
    pub fn get_pool_info(&self) -> (usize, usize) {
        (self.pool.size() as usize, self.pool.num_idle())
    }

    /// 关闭数据库连接池
    /// 
    /// 优雅地关闭所有数据库连接
    pub async fn close(&self) {
        self.pool.close().await;
        tracing::info!("🔒 数据库连接池已关闭");
    }
}

/// 用户数据访问层
impl DatabaseManager {
    /// 创建用户
    /// 
    /// # 参数
    /// - `request`: 用户创建请求
    /// 
    /// # 返回值
    /// - `Result<User, AppError>`: 创建成功返回用户信息，失败返回错误
    pub async fn create_user(&self, request: CreateUserRequest) -> AppResult<User> {
        // 验证密码确认
        if request.password != request.confirm_password {
            return Err(AppError::validation("密码和确认密码不匹配"));
        }

        // 验证密码强度
        if request.password.len() < 8 {
            return Err(AppError::validation("密码长度至少8个字符"));
        }

        // 验证邮箱格式
        if !request.email.contains('@') {
            return Err(AppError::validation("邮箱格式无效"));
        }

        // 哈希密码
        let password_hash = self.hash_password(&request.password)?;

        let user = sqlx::query_as!(
            User,
            r#"
            INSERT INTO users (
                username, email, password_hash, full_name, status, role, created_at, updated_at
            )
            VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW())
            RETURNING *
            "#,
            request.username,
            request.email,
            password_hash,
            request.full_name,
            UserStatus::Active as _,
            UserRole::User as _
        )
        .fetch_one(&self.pool)
        .await
        .map_err(|e| {
            if e.as_database_error().is_some() {
                AppError::conflict("用户名或邮箱已存在")
            } else {
                AppError::Database(e)
            }
        })?;

        tracing::info!("✅ 用户创建成功: {}", user.username);
        Ok(user)
    }

    /// 根据ID获取用户
    /// 
    /// # 参数
    /// - `id`: 用户ID
    /// 
    /// # 返回值
    /// - `Result<Option<User>, AppError>`: 找到用户返回Some，未找到返回None，失败返回错误
    pub async fn get_user_by_id(&self, id: Uuid) -> AppResult<Option<User>> {
        let user = sqlx::query_as!(
            User,
            "SELECT * FROM users WHERE id = $1",
            id
        )
        .fetch_optional(&self.pool)
        .await
        .map_err(AppError::Database)?;

        Ok(user)
    }

    /// 根据邮箱获取用户
    /// 
    /// # 参数
    /// - `email`: 用户邮箱
    /// 
    /// # 返回值
    /// - `Result<Option<User>, AppError>`: 找到用户返回Some，未找到返回None，失败返回错误
    pub async fn get_user_by_email(&self, email: &str) -> AppResult<Option<User>> {
        let user = sqlx::query_as!(
            User,
            "SELECT * FROM users WHERE email = $1",
            email
        )
        .fetch_optional(&self.pool)
        .await
        .map_err(AppError::Database)?;

        Ok(user)
    }

    /// 根据用户名获取用户
    /// 
    /// # 参数
    /// - `username`: 用户名
    /// 
    /// # 返回值
    /// - `Result<Option<User>, AppError>`: 找到用户返回Some，未找到返回None，失败返回错误
    pub async fn get_user_by_username(&self, username: &str) -> AppResult<Option<User>> {
        let user = sqlx::query_as!(
            User,
            "SELECT * FROM users WHERE username = $1",
            username
        )
        .fetch_optional(&self.pool)
        .await
        .map_err(AppError::Database)?;

        Ok(user)
    }

    /// 根据邮箱或用户名获取用户
    /// 
    /// # 参数
    /// - `email_or_username`: 邮箱或用户名
    /// 
    /// # 返回值
    /// - `Result<Option<User>, AppError>`: 找到用户返回Some，未找到返回None，失败返回错误
    pub async fn get_user_by_email_or_username(&self, email_or_username: &str) -> AppResult<Option<User>> {
        let user = sqlx::query_as!(
            User,
            "SELECT * FROM users WHERE email = $1 OR username = $1",
            email_or_username
        )
        .fetch_optional(&self.pool)
        .await
        .map_err(AppError::Database)?;

        Ok(user)
    }

    /// 更新用户信息
    /// 
    /// # 参数
    /// - `id`: 用户ID
    /// - `request`: 更新请求
    /// 
    /// # 返回值
    /// - `Result<User, AppError>`: 更新成功返回用户信息，失败返回错误
    pub async fn update_user(&self, id: Uuid, request: UpdateUserRequest) -> AppResult<User> {
        // 构建动态更新查询
        let mut query = String::from("UPDATE users SET updated_at = NOW()");
        let mut params: Vec<Box<dyn sqlx::Encode<'_, sqlx::Postgres> + Send + Sync>> = Vec::new();
        let mut param_count = 0;

        if let Some(username) = request.username {
            param_count += 1;
            query.push_str(&format!(", username = ${}", param_count));
            params.push(Box::new(username));
        }

        if let Some(email) = request.email {
            param_count += 1;
            query.push_str(&format!(", email = ${}", param_count));
            params.push(Box::new(email));
        }

        if let Some(full_name) = request.full_name {
            param_count += 1;
            query.push_str(&format!(", full_name = ${}", param_count));
            params.push(Box::new(full_name));
        }

        if let Some(avatar_url) = request.avatar_url {
            param_count += 1;
            query.push_str(&format!(", avatar_url = ${}", param_count));
            params.push(Box::new(avatar_url));
        }

        if let Some(status) = request.status {
            param_count += 1;
            query.push_str(&format!(", status = ${}", param_count));
            params.push(Box::new(status));
        }

        if let Some(role) = request.role {
            param_count += 1;
            query.push_str(&format!(", role = ${}", param_count));
            params.push(Box::new(role));
        }

        param_count += 1;
        query.push_str(&format!(" WHERE id = ${} RETURNING *", param_count));
        params.push(Box::new(id));

        // 执行更新查询
        let user = sqlx::query_as::<_, User>(&query)
            .fetch_one(&self.pool)
            .await
            .map_err(|e| {
                if e.as_database_error().is_some() {
                    AppError::conflict("用户名或邮箱已存在")
                } else if e.as_database_error().is_some() {
                    AppError::NotFound
                } else {
                    AppError::Database(e)
                }
            })?;

        tracing::info!("✅ 用户更新成功: {}", user.username);
        Ok(user)
    }

    /// 删除用户
    /// 
    /// # 参数
    /// - `id`: 用户ID
    /// 
    /// # 返回值
    /// - `Result<(), AppError>`: 删除成功返回Ok，失败返回错误
    pub async fn delete_user(&self, id: Uuid) -> AppResult<()> {
        let result = sqlx::query!(
            "DELETE FROM users WHERE id = $1",
            id
        )
        .execute(&self.pool)
        .await
        .map_err(AppError::Database)?;

        if result.rows_affected() == 0 {
            return Err(AppError::NotFound);
        }

        tracing::info!("✅ 用户删除成功: {}", id);
        Ok(())
    }

    /// 获取用户列表（分页）
    /// 
    /// # 参数
    /// - `query`: 查询参数
    /// 
    /// # 返回值
    /// - `Result<PaginatedResponse<User>, AppError>`: 分页用户列表
    pub async fn get_users(&self, query: UserQuery) -> AppResult<PaginatedResponse<User>> {
        let page = query.page.unwrap_or(1);
        let limit = query.limit.unwrap_or(20).min(100); // 最大100条
        let offset = (page - 1) * limit;

        // 构建WHERE子句
        let mut where_clause = String::new();
        let mut params: Vec<Box<dyn sqlx::Encode<'_, sqlx::Postgres> + Send + Sync>> = Vec::new();
        let mut param_count = 0;

        if let Some(search) = &query.search {
            if !search.is_empty() {
                param_count += 1;
                where_clause.push_str(&format!(
                    " AND (username ILIKE ${} OR email ILIKE ${} OR full_name ILIKE ${})",
                    param_count, param_count, param_count
                ));
                params.push(Box::new(format!("%{}%", search)));
            }
        }

        if let Some(status) = &query.status {
            param_count += 1;
            where_clause.push_str(&format!(" AND status = ${}", param_count));
            params.push(Box::new(status));
        }

        if let Some(role) = &query.role {
            param_count += 1;
            where_clause.push_str(&format!(" AND role = ${}", param_count));
            params.push(Box::new(role));
        }

        // 构建排序子句
        let sort_by = query.sort_by.as_deref().unwrap_or("created_at");
        let sort_order = match query.sort_order {
            Some(order) => match order {
                crate::models::SortOrder::Asc => "ASC",
                crate::models::SortOrder::Desc => "DESC",
            },
            None => "DESC",
        };
        let order_clause = format!(" ORDER BY {} {}", sort_by, sort_order);

        // 构建完整查询
        let full_query = format!(
            "SELECT * FROM users WHERE 1=1 {} {} LIMIT {} OFFSET {}",
            where_clause, order_clause, limit, offset
        );

        let count_query = format!("SELECT COUNT(*) FROM users WHERE 1=1 {}", where_clause);

        // 执行数据查询
        let users: Vec<User> = sqlx::query_as::<_, User>(&full_query)
            .fetch_all(&self.pool)
            .await
            .map_err(AppError::Database)?;

        // 执行计数查询
        let total: i64 = sqlx::query_scalar(&count_query)
            .fetch_one(&self.pool)
            .await
            .map_err(AppError::Database)?;

        Ok(PaginatedResponse::new(users, page, limit, total as u64))
    }

    /// 更新用户最后登录时间
    /// 
    /// # 参数
    /// - `id`: 用户ID
    /// 
    /// # 返回值
    /// - `Result<(), AppError>`: 更新成功返回Ok，失败返回错误
    pub async fn update_last_login(&self, id: Uuid) -> AppResult<()> {
        sqlx::query!(
            "UPDATE users SET last_login_at = NOW(), updated_at = NOW() WHERE id = $1",
            id
        )
        .execute(&self.pool)
        .await
        .map_err(AppError::Database)?;

        Ok(())
    }

    /// 验证用户密码
    /// 
    /// # 参数
    /// - `password`: 明文密码
    /// - `hash`: 密码哈希
    /// 
    /// # 返回值
    /// - `Result<bool, AppError>`: 密码正确返回true，错误返回false，失败返回错误
    pub fn verify_password(&self, password: &str, hash: &str) -> AppResult<bool> {
        use argon2::{Argon2, PasswordHash, PasswordVerifier};

        let argon2 = Argon2::default();
        let parsed_hash = PasswordHash::new(hash)
            .map_err(|e| AppError::PasswordHash(e.to_string()))?;

        Ok(argon2.verify_password(password.as_bytes(), &parsed_hash).is_ok())
    }

    /// 哈希密码
    /// 
    /// # 参数
    /// - `password`: 明文密码
    /// 
    /// # 返回值
    /// - `Result<String, AppError>`: 哈希后的密码字符串
    fn hash_password(&self, password: &str) -> AppResult<String> {
        use argon2::{Argon2, PasswordHasher};
        use argon2::password_hash::{rand_core::OsRng, SaltString};

        let salt = SaltString::generate(&mut OsRng);
        let argon2 = Argon2::default();
        let password_hash = argon2
            .hash_password(password.as_bytes(), &salt)
            .map_err(|e| AppError::PasswordHash(e.to_string()))?;

        Ok(password_hash.to_string())
    }
}

/// 实现配置的数据库池选项方法
impl DatabaseConfig {
    /// 获取数据库连接池选项
    /// 
    /// # 返回值
    /// - `sqlx::postgres::PgPoolOptions`: 配置好的连接池选项
    pub fn database_pool_options(&self) -> sqlx::postgres::PgPoolOptions {
        sqlx::postgres::PgPoolOptions::new()
            .max_connections(self.max_connections)
            .min_connections(self.min_connections)
            .acquire_timeout(Duration::from_secs(self.acquire_timeout))
            .idle_timeout(Duration::from_secs(self.idle_timeout))
            .max_lifetime(Duration::from_secs(self.max_lifetime))
            .test_before_acquire(false)
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[tokio::test]
    async fn test_password_hashing() {
        let config = DatabaseConfig::default();
        let db = DatabaseManager::new(&config).await.unwrap();
        
        let password = "test_password_123";
        let hash = db.hash_password(password).unwrap();
        
        // 验证密码
        assert!(db.verify_password(password, &hash).unwrap());
        assert!(!db.verify_password("wrong_password", &hash).unwrap());
    }

    #[tokio::test]
    async fn test_user_crud() {
        let config = DatabaseConfig::default();
        let db = DatabaseManager::new(&config).await.unwrap();

        // 创建用户
        let create_request = CreateUserRequest {
            username: "test_user".to_string(),
            email: "test@example.com".to_string(),
            password: "test_password_123".to_string(),
            confirm_password: "test_password_123".to_string(),
            full_name: Some("Test User".to_string()),
        };

        let user = db.create_user(create_request).await.unwrap();
        assert_eq!(user.username, "test_user");
        assert_eq!(user.email, "test@example.com");

        // 获取用户
        let found_user = db.get_user_by_id(user.id).await.unwrap();
        assert!(found_user.is_some());
        assert_eq!(found_user.unwrap().username, "test_user");

        // 更新用户
        let update_request = UpdateUserRequest {
            username: Some("updated_user".to_string()),
            full_name: Some("Updated User".to_string()),
            ..Default::default()
        };

        let updated_user = db.update_user(user.id, update_request).await.unwrap();
        assert_eq!(updated_user.username, "updated_user");

        // 删除用户
        db.delete_user(user.id).await.unwrap();
        
        // 验证删除
        let deleted_user = db.get_user_by_id(user.id).await.unwrap();
        assert!(deleted_user.is_none());
    }
}
